package com.dy.yunying.biz.dao.znfx;

import com.dy.yunying.api.req.znfx.AdPlanOverviewDto;
import com.dy.yunying.api.resp.znfx.AdPlanDataExportRes;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author chenxiang
 * @className AdPlanAnalysisDao
 * @date 2023-3-24 11:29
 */
@Slf4j
@Component(value = "adPlanAnalyseReportDao")
public class AdPlanAnalyseReportDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;
	@Resource
	private YunYingProperties yunYingProperties;


	/**
	 * 计划属性分析导出列表查询
	 * @param req
	 * @return
	 */
	public List<AdPlanDataExportRes> list(AdPlanOverviewDto req) {
		final String indentStr = StringUtils.EMPTY;
		final StringBuilder sql = this.getResultSql(req, indentStr);

		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}

		log.info("计划属性分析导出列表查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();

		List<AdPlanDataExportRes> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(AdPlanDataExportRes.class));
		long end = System.currentTimeMillis();
		log.info("计划属性分析导出列表查询耗时: {}ms", end - start);
		return list;
	}

	private StringBuilder getResultSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder sql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		sql.append(indentStr).append("SELECT\n");
		sql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(" AS period, ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(queryColumn) ? queryColumn + ", " : StringUtils.EMPTY);
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("adid")) {
			sql.append(indentStr).append(" adidName,  ");
		}
		sql.append(indentStr).append("showRatio,\n");
		sql.append(indentStr).append("    shownums shownums, -- 展示数\n");
		sql.append(indentStr).append("    clicknums clicknums, -- 点击数\n");
		sql.append(indentStr).append("    round(rudeCost, 2) rudeCost, -- 原始消耗\n");
		sql.append(indentStr).append("    round(cost, 2) cost, -- 返点后消耗\n");
		sql.append(indentStr).append("    uuidnums, -- 新增设备数\n");
		sql.append(indentStr).append("    usrnamenums, -- 新增注册设备数\n");
		sql.append(indentStr).append("    deduplicateDeviceCount, -- 去重设备数\n");
		sql.append(indentStr).append("    returnDeviceCount, -- 回归设备数\n");
		sql.append(indentStr).append("    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("    youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("    retention2, -- 次留人数\n");
		sql.append(indentStr).append("    activedevices AS activeNum, -- 活跃设备数\n");
		sql.append(indentStr).append("    usrpaynamenums AS paydevice1, -- 新增付费设备数\n");
		sql.append(indentStr).append("    payNum, -- 活跃付费次数\n");
		sql.append(indentStr).append("    payNum1, -- 首日付费次数\n");
		sql.append(indentStr).append("    payNum7, -- 首周付费次数\n");
		sql.append(indentStr).append("    round(IF(toInt64(paydevice1) > 0, divide(toFloat64(cost), paydevice1), 0), 2) AS payCose1, -- 首日付费成本\n");
		sql.append(indentStr).append("    round(IF(toInt64(paydeviceAll) > 0, divide(toFloat64(cost), paydeviceAll), 0), 2) AS payCoseAll, -- 累计付费成本\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(newdevicesharfee), 2), toDecimal64OrZero(toString(newdevicefees), 2)) AS newPayFee, -- 新增充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(newdeviceshargivemoney), 2), toDecimal64OrZero(toString(newdevicegivemoney), 2)) AS newPayGivemoney, -- 新增充值代金券金额\n");
		sql.append(indentStr).append("    weektotalfeenums AS paydevice7, -- 当周付费设备数\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(weeksharfee), 2), toDecimal64OrZero(toString(weektotalfee), 2)) AS weekPayFee, -- 当周充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(weekshargivemoney), 2), toDecimal64OrZero(toString(weektotalgivemoney), 2)) AS weekPayGivemoney, -- 当周充值代金券金额\n");
		sql.append(indentStr).append("    monthtotalfeenums AS paydevice30, -- 当月付费设备数\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(monthsharfee), 4), toDecimal64OrZero(toString(monthtotalfee), 4)) AS monthPayFee, -- 当月充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(monthshargivemoney), 4), toDecimal64OrZero(toString(monthtotalgivemoney), 4)) AS monthPayGivemoney, -- 当月充值代金券金额\n");
		sql.append(indentStr).append("    periodPayCount, -- 期内付费设备数 期内充值人数\n");
		sql.append(indentStr).append("    round(IF(toInt64(periodPayCount) > 0, divide(toFloat64(cost), periodPayCount), 0), 2) AS periodPayCose, -- 期内付费成本\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(duplicateDeviceCount * 100.00, usrnamenums), 0), 2) AS duplicateDeviceRatio, -- 设备重复率\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(periodPayFee2), 4), toDecimal64OrZero(toString(periodPayFee1), 4)) AS periodPayFee, -- 期内充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(periodPayGivemoney2), 4), toDecimal64OrZero(toString(periodPayGivemoney1), 4)) AS periodPayGivemoney, -- 期内充值代金券金额\n");
		sql.append(indentStr).append("    totalPayfeenums AS paydeviceAll, -- 累计付费设备数\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(totalPaysharfee), 2), toDecimal64OrZero(toString(totalPayfee), 2)) AS totalPayFee, -- 累计充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(totalPayshargivemoney), 2), toDecimal64OrZero(toString(totalPaygivemoney), 2)) AS totalPayGivemoney, -- 累计充值代金券金额\n");
		sql.append(indentStr).append("    activepaydevices AS activePayCount, -- 活跃设备付费数\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(activesharfee), 2), toDecimal64OrZero(toString(activetotalfee), 2)) AS activePayFee, -- 活跃充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(activeshargivemoney), 2), toDecimal64OrZero(toString(activetotalgivemoney), 2)) AS activePayGivemoney, -- 活跃充值代金券金额\n");
		sql.append(indentStr).append("    round(IF(toInt64(shownums) > 0, divide(clicknums * 100.00, shownums), 0), 2) AS clickRatio, -- 点击率\n");
		sql.append(indentStr).append("    round(IF(toInt64(clicknums) > 0, divide(usrnamenums * 100.00, clicknums), 0), 2) AS regRatio, -- 点击注册率\n");
		sql.append(indentStr).append("    round(IF(toInt64(uuidnums) > 0, divide(usrnamenums * 100.00, uuidnums), 0), 2) AS activationRatio, -- 激活注册率\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(toFloat64(cost), usrnamenums), 0), 2) AS deviceCose, -- 设备成本\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(roleDeviceCount * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率\n");
		sql.append(indentStr).append("    round(IF(toInt64(newUserCount) > 0, divide(certifiedCount * 100.00, newUserCount), 0), 2) AS certifiedRate, -- 新增实名制转化率\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(paydeviceAll * 100.00, usrnamenums), 0), 2) AS totalPayRate, -- 累计付费率\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(retention2 * 100.00, usrnamenums), 0), 2) AS retention2Ratio, -- 次留\n");
		sql.append(indentStr).append("    CAST(CASE WHEN payedRegisterCount > 0 THEN round(payedRetention2Count * 100 / IF(payedRegisterCount > 0, payedRegisterCount, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS payedRetention2, -- 付费次留\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(usrpaynamenums * 100.00, usrnamenums), 0), 2) AS regPayRatio, -- 新增付费率\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(usrnamenums) > 0, divide(toFloat64(newdevicesharfee), usrnamenums), 0), 2),round(IF(toInt64(usrnamenums) > 0, divide(toFloat64(newdevicefees), usrnamenums), 0), 2)) AS regarpu, -- 新增注册ARPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(paydevice1) > 0, divide(toFloat64(newdevicesharfee), paydevice1), 0), 2),round(IF(toInt64(paydevice1) > 0, divide(toFloat64(newdevicefees), paydevice1), 0), 2)) AS payarppu, -- 新增付费ARPPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(newdevicesharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(newdevicefees * 100.00, cost), 0), 2)) roi1, -- 首日ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(weeksharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(weektotalfee * 100.00, cost), 0), 2)) weekRoi, -- 当周ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(monthsharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(monthtotalfee * 100.00, cost), 0), 2)) monthRoi, -- 当月ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(day3devicesharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(day3devicefees * 100.00, cost), 0), 2)) roi3, -- 3日ROI \n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(day7devicesharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(day7devicefees * 100.00, cost), 0), 2)) roi7, -- 7日ROI \n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(day15devicesharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(day15devicefees * 100.00, cost), 0), 2)) roi15, -- 15日ROI \n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(day30devicesharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(day30devicefees * 100.00, cost), 0), 2)) roi30, -- 30日ROI \n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(periodPayCount * 100.00, usrnamenums), 0), 2) AS periodPayRate, -- 期内付费率\n");
		sql.append(indentStr).append("    round(IF(cost > 0, divide(toFloat64(periodPayFee) * 100.00, cost), 0), 2) AS periodROI, -- 期内ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(totalPaysharfee * 100.00, cost), 0), 2), round(IF(cost > 0, divide(totalPayfee * 100.00, cost), 0), 2)) allRoi, -- 累计ROI\n");
		sql.append(indentStr).append("    round(IF(toInt64(activedevices) > 0, divide(activepaydevices * 100.00, activedevices), 0), 2) AS activePayRate, -- 活跃付费率\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(activedevices) > 0, divide(toFloat64(activesharfee), activedevices), 0), 2), round(IF(toInt64(activedevices) > 0, divide(toFloat64(activetotalfee), activedevices), 0), 2)) actarpu, -- 活跃设备ARPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(activePayCount) > 0, divide(toFloat64(activesharfee), activePayCount), 0), 2), round(IF(toInt64(activePayCount) > 0, divide(toFloat64(activetotalfee), activePayCount), 0), 2)) activearppu -- 活跃付费ARPPU\n");
		sql.append(indentStr).append("FROM\n");
		sql.append(indentStr).append("    (\n");
		sql.append(this.getSql(req, indentStr + "        "));
		sql.append(indentStr).append("    ) t1000 \n");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("adid")) {
				sql.append(indentStr).append("    LEFT JOIN (\n");
				sql.append(indentStr).append("        SELECT\n");
				sql.append(indentStr).append("            adid adidTmp, name adidName, ctype ctype \n");
				sql.append(indentStr).append("        FROM\n");
				sql.append(indentStr).append("            ").append(yunYingProperties.getPanguadidtable()).append('\n');
				sql.append(indentStr).append("    ) t2000 ON adid = t2000.adidTmp\n");
			}
		}
		sql.append(indentStr).append("    WHERE 1=1  \n");
		if (1 == req.getType()){
			sql.append(indentStr).append("    AND cost > 0 \n");
		}
		sql.append(indentStr).append(this.getCostWhereSql(req));
		return sql;
	}

	private StringBuilder getPeriodSql(AdPlanOverviewDto req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	private StringBuilder getSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr).append("SELECT\n");
		sql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append(req.getShowRatio()).append(" showRatio,\n");
		sql.append(indentStr).append("    toFloat64(rudeCost) rudeCost, -- 原始消耗\n");
		sql.append(indentStr).append("    toFloat64(cost) cost, -- 返点后消耗\n");
		sql.append(indentStr).append("    COALESCE(clicknums, 0) clicknums, --点击数\n");
		sql.append(indentStr).append("    COALESCE(shownums, 0) shownums, --展示数\n");
		sql.append(indentStr).append("    COALESCE(uuidnums, 0) uuidnums, --激活数\n");
		sql.append(indentStr).append("    COALESCE(usrnamenums, 0) usrnamenums, --新增设备注册数\n");
		sql.append(indentStr).append("    COALESCE(retention2, 0)  retention2, --次留\n");
		sql.append(indentStr).append("    COALESCE(usrpaynamenums, 0) usrpaynamenums, --新增设备付费数\n");
		sql.append(indentStr).append("    toFloat64(newdevicefees) newdevicefees, --新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(newdevicesharfee) newdevicesharfee, --新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(newdevicegivemoney) newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(newdeviceshargivemoney) newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(day3devicefees) day3devicefees, --3日充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(day3devicesharfee) day3devicesharfee, --3日充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(day7devicefees) day7devicefees, --7日充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(day7devicesharfee) day7devicesharfee, --7日充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(day15devicefees) day15devicefees, --15日充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(day15devicesharfee) day15devicesharfee, --15日充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(day30devicefees) day30devicefees, --30日充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(day30devicesharfee) day30devicesharfee, --30日充值实付金额（分成后）\n");
		sql.append(indentStr).append("    COALESCE(weektotalfeenums,0) weektotalfeenums, --当周充值人数\n");
		sql.append(indentStr).append("    toFloat64(weektotalfee) weektotalfee, --当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(weeksharfee) weeksharfee, --当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(weektotalgivemoney) weektotalgivemoney, --当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(weekshargivemoney) weekshargivemoney, --当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    COALESCE(monthtotalfeenums,0) monthtotalfeenums, --当月充值人数\n");
		sql.append(indentStr).append("    toFloat64(monthtotalfee) monthtotalfee, --当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(monthsharfee) monthsharfee, --当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(monthtotalgivemoney) monthtotalgivemoney, --当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(monthshargivemoney) monthshargivemoney, --当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    COALESCE(totalPayfeenums,0) totalPayfeenums, --累计充值人数\n");
		sql.append(indentStr).append("    toFloat64(totalPayfee) totalPayfee, --累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(totalPaysharfee) totalPaysharfee, --累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(totalPaygivemoney) totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(totalPayshargivemoney) totalPayshargivemoney, --累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    COALESCE(activedevices,0) activedevices, --活跃设备数\n");
		sql.append(indentStr).append("    COALESCE(activepaydevices,0) activepaydevices, --活跃设备付费数\n");
		sql.append(indentStr).append("    toFloat64(activetotalfee) activetotalfee, --活跃充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(activesharfee)  activesharfee, --活跃充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(activetotalgivemoney) activetotalgivemoney, --活跃充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(activeshargivemoney) activeshargivemoney, --活跃充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    COALESCE(periodPayCount, 0) periodPayCount, --期内设备付费数\n");
		sql.append(indentStr).append("    toFloat64(periodPayFee1) periodPayFee1, --期内充值实付金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(periodPayFee2) periodPayFee2, --期内充值实付金额（分成后）\n");
		sql.append(indentStr).append("    toFloat64(periodPayGivemoney1) periodPayGivemoney1, --期内充值代金券金额（分成前）\n");
		sql.append(indentStr).append("    toFloat64(periodPayGivemoney2) periodPayGivemoney2, --期内充值代金券金额（分成后）\n");
		sql.append(indentStr).append("    deduplicateDeviceCount, -- 去重设备数\n");
		sql.append(indentStr).append("    returnDeviceCount, -- 回归设备数\n");
		sql.append(indentStr).append("    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("    payedRegisterCount, -- 付费注册数\n");
		sql.append(indentStr).append("    payedRetention2Count, -- 付费留存数\n");
		sql.append(indentStr).append("    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("    roleDeviceCount, -- 创角的新增设备数\n");
		sql.append(indentStr).append("    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("    youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("    COALESCE(payNum,0) payNum, -- 活跃付费次数\n");
		sql.append(indentStr).append("    payNum1, -- 首日付费次数\n");
		sql.append(indentStr).append("    payNum7 -- 首周付费次数\n");
		sql.append(indentStr).append("FROM\n");
		sql.append(indentStr).append("    (\n");
		sql.append(indentStr).append("        SELECT\n");
		sql.append(indentStr).append("            ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		sql.append(indentStr).append("            rudeCost, --原始消耗\n");
		sql.append(indentStr).append("            cost, --返点后消耗\n");
		sql.append(indentStr).append("            shownums, --展示数\n");
		sql.append(indentStr).append("            clicknums, --点击数\n");
		sql.append(indentStr).append("            uuidnums, --新增设备注册数\n");
		sql.append(indentStr).append("            usrnamenums, --新增设备注册数\n");
		sql.append(indentStr).append("            retention2, --留存\n");
		sql.append(indentStr).append("            usrpaynamenums, --新增设备付费数\n");
		sql.append(indentStr).append("            newdevicefees, --新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("            newdevicesharfee, --新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("            newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            day3devicefees, --3日充值实付金额（分成前）\n");
		sql.append(indentStr).append("            day3devicesharfee, --3日充值实付金额（分成后）\n");
		sql.append(indentStr).append("            day7devicefees, --7日充值实付金额（分成前）\n");
		sql.append(indentStr).append("            day7devicesharfee, --7日充值实付金额（分成后）\n");
		sql.append(indentStr).append("            day15devicefees, --15日充值实付金额（分成前）\n");
		sql.append(indentStr).append("            day15devicesharfee, --15日充值实付金额（分成后）\n");
		sql.append(indentStr).append("            day30devicefees, --30日充值实付金额（分成前）\n");
		sql.append(indentStr).append("            day30devicesharfee, --30日充值实付金额（分成后）\n");
		sql.append(indentStr).append("            weektotalfeenums, --当周充值人数\n");
		sql.append(indentStr).append("            weektotalfee, --当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("            weeksharfee, --当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("            weektotalgivemoney, --当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            weekshargivemoney, --当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            monthtotalfeenums, --当月充值人数\n");
		sql.append(indentStr).append("            monthtotalfee, --当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("            monthsharfee, --当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("            monthtotalgivemoney, --当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            monthshargivemoney, --当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            totalPayfeenums, --累计充值人数\n");
		sql.append(indentStr).append("            totalPayfee, --累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("            totalPaysharfee, --累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("            totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            periodPayCount, -- 期内设备付费数\n");
		sql.append(indentStr).append("            periodPayFee1, -- 期内充值实付金额（分成前）\n");
		sql.append(indentStr).append("            periodPayFee2, -- 期内充值实付金额（分成后）\n");
		sql.append(indentStr).append("            periodPayGivemoney1, -- 期内充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            periodPayGivemoney2, -- 期内充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            deduplicateDeviceCount, -- 去重设备数\n");
		sql.append(indentStr).append("            returnDeviceCount, -- 回归设备数\n");
		sql.append(indentStr).append("            duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("            payedRegisterCount, -- 付费注册数\n");
		sql.append(indentStr).append("            payedRetention2Count, -- 付费留存数\n");
		sql.append(indentStr).append("            newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("            createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("            roleDeviceCount, -- 创角的新增设备数\n");
		sql.append(indentStr).append("            certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("            notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("            youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("            payNum1, -- 首日付费次数\n");
		sql.append(indentStr).append("            payNum7 -- 首周付费次数\n");
		sql.append(indentStr).append("        FROM\n");
		sql.append(indentStr).append("            (\n");
		sql.append(indentStr).append("                SELECT\n");
		sql.append(indentStr).append("                    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		sql.append(indentStr).append("                    rudeCost, --原始消耗\n");
		sql.append(indentStr).append("                    cost, --返点后消耗\n");
		sql.append(indentStr).append("                    shownums, --展示数\n");
		sql.append(indentStr).append("                    clicknums, --点击数\n");
		sql.append(indentStr).append("                    uuidnums, --新增设备注册数\n");
		sql.append(indentStr).append("                    usrnamenums, --新增设备注册数\n");
		sql.append(indentStr).append("                    retention2, --留存\n");
		sql.append(indentStr).append("                    usrpaynamenums, --新增设备付费数\n");
		sql.append(indentStr).append("                    newdevicefees, --新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    newdevicesharfee, --新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    day3devicefees, --3日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    day3devicesharfee, --3日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    day7devicefees, --7日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    day7devicesharfee, --7日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    day15devicefees, --15日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    day15devicesharfee, --15日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    day30devicefees, --30日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    day30devicesharfee, --30日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalfeenums, --当周充值人数\n");
		sql.append(indentStr).append("                    weektotalfee, --当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    weeksharfee, --当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalgivemoney, --当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    weekshargivemoney, --当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalfeenums, --当月充值人数\n");
		sql.append(indentStr).append("                    monthtotalfee, --当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    monthsharfee, --当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalgivemoney, --当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    monthshargivemoney, --当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    totalPayfeenums, --累计充值人数\n");
		sql.append(indentStr).append("                    totalPayfee, --累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    totalPaysharfee, --累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    totalPayshargivemoney, --累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    deduplicateDeviceCount, -- 去重设备数\n");
		sql.append(indentStr).append("                    returnDeviceCount, -- 回归设备数\n");
		sql.append(indentStr).append("                    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("                    payedRegisterCount, -- 付费注册数\n");
		sql.append(indentStr).append("                    payedRetention2Count, -- 付费留存数\n");
		sql.append(indentStr).append("                    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("                    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("                    roleDeviceCount, -- 创角的新增设备数\n");
		sql.append(indentStr).append("                    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("                    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("                    youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("                    payNum1, -- 首日付费次数\n");
		sql.append(indentStr).append("                    payNum7 -- 首周付费次数\n");
		sql.append(indentStr).append("                FROM\n");
		sql.append(indentStr).append("                    (\n");
		sql.append(indentStr).append("                        SELECT\n");
		sql.append(indentStr).append("                            ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		sql.append(indentStr).append("                            usrnamenums,-- 新增设备注册数\n");
		sql.append(indentStr).append("                            uuidnums, --新增设备\n");
		sql.append(indentStr).append("                            retention2, --次留\n");
		sql.append(indentStr).append("                            usrpaynamenums, --新增设备付费数\n");
		sql.append(indentStr).append("                            newdevicefees, --新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            newdevicesharfee, --新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            day3devicefees, --3日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            day3devicesharfee, --3日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            day7devicefees, --7日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            day7devicesharfee, --7日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            day15devicefees, --15日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            day15devicesharfee, --15日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            day30devicefees, --30日充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            day30devicesharfee, --30日充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            weektotalfeenums, --当周充值人数\n");
		sql.append(indentStr).append("                            weektotalfee, --当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            weeksharfee, --当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            weektotalgivemoney, --当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            weekshargivemoney, --当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            monthtotalfeenums, --当月充值人数\n");
		sql.append(indentStr).append("                            monthtotalfee, --当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            monthsharfee, --当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            monthtotalgivemoney, --当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            monthshargivemoney, --当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            totalPayfeenums, --累计充值人数\n");
		sql.append(indentStr).append("                            totalPayfee, --累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            totalPaysharfee, --累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            totalPayshargivemoney, --累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            deduplicateDeviceCount, -- 去重设备数\n");
		sql.append(indentStr).append("                            returnDeviceCount, -- 回归设备数\n");
		sql.append(indentStr).append("                            duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("                            payedRegisterCount, -- 付费注册数\n");
		sql.append(indentStr).append("                            payedRetention2Count, -- 付费留存数\n");
		sql.append(indentStr).append("                            newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("                            createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("                            roleDeviceCount, -- 创角的新增设备数\n");
		sql.append(indentStr).append("                            certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("                            notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("                            youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("                            payNum1, -- 首日付费次数\n");
		sql.append(indentStr).append("                            payNum7 -- 首周付费次数\n");
		sql.append(indentStr).append("                        FROM\n");
		sql.append(indentStr).append("                            (\n");
		sql.append(this.getDeviceRegSql(req, indentStr + "                                "));
		sql.append(indentStr).append("                            ) a\n");
		sql.append(indentStr).append("                            FULL JOIN (\n");
		sql.append(this.getCertifiedSql(req, indentStr + "                                "));
		sql.append(indentStr).append("                            ) b USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? ", " + req.getQueryColumn() : StringUtils.EMPTY).append(")\n"); // USING 条件
		sql.append(indentStr).append("                    ) c\n");
		sql.append(indentStr).append("                    FULL JOIN (\n");
		sql.append(this.getCostSql(req, indentStr + "                        "));
		sql.append(indentStr).append("                    ) d USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? ", " + req.getQueryColumn() : StringUtils.EMPTY).append(")\n"); // USING 条件
		sql.append(indentStr).append("            ) e\n");
		sql.append(indentStr).append("            FULL JOIN (\n");
		sql.append(this.getPeriodPaySql(req, indentStr + "                "));
		sql.append(indentStr).append("            ) f USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? ", " + req.getQueryColumn() : StringUtils.EMPTY).append(")\n"); // USING 条件
		sql.append(indentStr).append("    ) g\n");
		sql.append(indentStr).append("    FULL JOIN (\n");
		sql.append(this.getActiveDeviceSql(req, indentStr + "        "));
		sql.append(indentStr).append("    ) h USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? ", " + req.getQueryColumn() : StringUtils.EMPTY).append(")\n"); // USING 条件
		return sql;
	}


	//新增充值金额和新增设备付费数,当周充值金额，当月充值金额，累计充值金额
	private StringBuilder getDeviceRegSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder deviceRegSql = new StringBuilder();
		deviceRegSql.append(indentStr).append("SELECT\n");
		deviceRegSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append("\n");
		deviceRegSql.append(indentStr).append("    COALESCE(COUNT(IF(reg.latest_username != '', reg.latest_username, NULL)), 0) usrnamenums,-- 新增设备注册数\n");
		deviceRegSql.append(indentStr).append("    COUNT(distinct reg.kid) uuidnums, --新增设备\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(is_2_retention), 0) retention2, --次留\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_1 > 0 OR reg.givemoney_1 > 0, 1, 0)), 0) usrpaynamenums, --新增设备付费数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_1), 0) newdevicefees, --新增充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_1 * sharing), 0) newdevicesharfee, --新增充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_1), 0) newdevicegivemoney, --新增充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_1 * sharing), 0) newdeviceshargivemoney, --新增充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_3), 0) day3devicefees, --3日充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_3 * sharing), 0) day3devicesharfee, --3日充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_7), 0) day7devicefees, --7日充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_7 * sharing), 0) day7devicesharfee, --7日充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_15), 0) day15devicefees, --15日充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_15 * sharing), 0) day15devicesharfee, --15日充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_30), 0) day30devicefees, --30日充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_30 * sharing), 0) day30devicesharfee, --30日充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_week > 0 OR reg.givemoney_week > 0, 1, 0)), 0) weektotalfeenums, --当周充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_week),0) weektotalfee, --当周充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_week * sharing), 0) weeksharfee, --当周充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_week),0) weektotalgivemoney, --当周充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_week * sharing), 0) weekshargivemoney, --当周充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_month > 0 OR reg.givemoney_month > 0, 1, 0)),0) monthtotalfeenums, --当月充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_month), 0) monthtotalfee, --当月充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_month * sharing), 0) monthsharfee, --当月充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_month), 0) monthtotalgivemoney, --当月充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_month * sharing), 0) monthshargivemoney, --当月充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_total > 0 OR reg.givemoney_total > 0, 1, 0)), 0) totalPayfeenums, --累计充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_total), 0) totalPayfee, --累计充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_total * sharing), 0) totalPaysharfee, --累计充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_total), 0) totalPaygivemoney, --累计充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_total * sharing), 0) totalPayshargivemoney, --累计充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.deduplicate_device), 0) deduplicateDeviceCount, -- 去重设备数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.return_device), 0) returnDeviceCount, -- 回归设备数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.duplicate_device), 0) duplicateDeviceCount, -- 重复设备数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.payed_register_count), 0) payedRegisterCount, -- 付费注册数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.payed_retention2_count), 0) payedRetention2Count, -- 付费留存数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.pay_num_1), 0) payNum1, -- 首日付费次数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.pay_num_7), 0) payNum7 -- 首周付费次数\n");
		deviceRegSql.append(indentStr).append("FROM\n");
		deviceRegSql.append(indentStr).append("    (\n");
		deviceRegSql.append(indentStr).append("        SELECT\n");
		deviceRegSql.append(indentStr).append("            reg_day day, week, month, year, kid, collect, game_main pgid, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id adid, ad_account advertiserid, convert_name convertName,\n");
		deviceRegSql.append(indentStr).append("            deep_convert deepConvert, latest_username, is_2_retention, fee_1, givemoney_1,fee_3, fee_7, fee_15, fee_30, fee_week, givemoney_week, fee_month, givemoney_month, fee_total, givemoney_total,\n");
		deviceRegSql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, 1, 0) AS payed_register_count, IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_2_retention, 0) AS payed_retention2_count,\n");
		deviceRegSql.append(indentStr).append("            IF(before_last_login_time_same > 0 OR before_last_login_time_diff > 0, 1, 0) AS deduplicate_device, IF(before_last_login_time_same > 0, 1, 0) AS return_device, IF(before_last_login_time_diff > 0, 1, 0) AS duplicate_device,pay_num_1,pay_num_7\n");
		deviceRegSql.append(indentStr).append("        FROM\n");
		deviceRegSql.append(indentStr).append("            ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		deviceRegSql.append(indentStr).append("        WHERE\n");
		deviceRegSql.append(indentStr).append("            spread_type = 1 AND ad_id <> '' AND reg_day >= ").append(req.getStartTime()).append(" AND reg_day <= ").append(req.getEndTime()).append(this.directDeviceRegisterWhereSnippet(req, " ")).append(this.directAdvertiseWhereSnippet(req, " ")).append('\n');
		deviceRegSql.append(indentStr).append("    ) reg\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name, '-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		deviceRegSql.append(indentStr).append("WHERE\n");
		deviceRegSql.append(indentStr).append("    1 = 1");
		deviceRegSql.append(indentStr).append("GROUP BY\n");
		deviceRegSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			deviceRegSql.append(", ").append(req.getQueryColumn());
		}
		deviceRegSql.append('\n'); // group by 条件
		return deviceRegSql;
	}


	private StringBuilder getCertifiedSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder certifiedSql = new StringBuilder();
		certifiedSql.append(indentStr).append("SELECT\n");
		certifiedSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		certifiedSql.append(indentStr).append("    COALESCE(COUNT(username), 0) AS newUserCount, -- 新增用户数\n");
		certifiedSql.append(indentStr).append("    COUNT(DISTINCT  IF (createRoleCount2>0,kid, NULL)) AS roleDeviceCount, -- 创角的新增设备数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(createRoleCount), 0) AS createRoleCount, -- 新增注册创角数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(certifiedMark), 0) AS certifiedCount, -- 新增注册实名数\n");
		certifiedSql.append(indentStr).append("    (newUserCount - certifiedCount) AS notCertifiedCount, -- 注册未实名数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(youngMark), 0) AS youngCount -- 未成年人数\n");
		certifiedSql.append(indentStr).append("FROM\n");
		certifiedSql.append(indentStr).append("    (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            MIN(d_reg_day) day, any(d_reg_week) week, any(d_reg_month) month, any(d_reg_year) year, any(d_kid) kid, any(collect) collect, d_game_main pgid, argMin(d_os, reg_time) os, argMin(d_game_sub, reg_time) gameid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_chl_main, reg_time) parentchl, argMin(d_chl_sub, reg_time) chl, argMin(d_chl_base, reg_time) appchl, argMin(d_ad_id, reg_time) adid, argMin(d_ad_account, reg_time) advertiserid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_convert_name, reg_time) convertName, argMin(d_deep_convert, reg_time) deepConvert, username\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            ").append(yunYingProperties.getAccountregistertable()).append(" acc\n");
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            d_spread_type = 1 AND d_ad_id <> '' AND d_reg_day >= ").append(req.getStartTime()).append(" AND d_reg_day <= ").append(req.getEndTime()).append(this.indirectDeviceRegisterWhereSnippet(req, " ")).append(this.indirectAdvertiseWhereSnippet(req, " ")).append('\n');
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgid, username\n");
		certifiedSql.append(indentStr).append("    ) acc\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgame_id) AS pgame_id, user_name,COUNT(role_id) AS createRoleCount2, COUNT(role_id) AS createRoleCount\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            (SELECT pgame_id, role_id, argMin(username, create_time) AS user_name FROM v_odsmysql_game_role GROUP BY pgame_id, role_id)\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) cr ON acc.pgid = cr.pgame_id AND acc.username = cr.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgid) AS pgame_id, ai user_name, argMax(id_num, check_time) AS id_num, 1 AS certifiedMark, IF(toDate(addYears(parseDateTimeBestEffortOrZero(SUBSTRING(id_num, 7, 8)), 18)) >= today(), 1, 0) youngMark\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            v_odsmysql_wan_user_realname\n");
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            status <> 2\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) rel ON acc.pgid = rel.pgame_id AND acc.username = rel.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = acc.parentchl AND wpc.chncode = acc.chl\n");
		certifiedSql.append(indentStr).append("WHERE\n");
		certifiedSql.append(indentStr).append("    1 = 1 ");
		certifiedSql.append(indentStr).append("GROUP BY\n");
		certifiedSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			certifiedSql.append(", ").append(req.getQueryColumn());
		}
		certifiedSql.append('\n'); // group by 条件
		return certifiedSql;
	}


	//	消耗、返点后消耗
	private StringBuilder getCostSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder costSql = new StringBuilder();
		costSql.append(indentStr).append("SELECT\n");
		costSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		costSql.append(indentStr).append("    COALESCE(SUM(rudeCost), 0) rudeCost, --原始消耗\n");
		costSql.append(indentStr).append("    COALESCE(SUM(cost), 0) cost, --返点后消耗\n");
		costSql.append(indentStr).append("    COALESCE(SUM(shownums), 0) shownums, --展示数\n");
		costSql.append(indentStr).append("    COALESCE(SUM(clicknums), 0) clicknums --点击数\n");
		costSql.append(indentStr).append("FROM\n");
		costSql.append(indentStr).append("    (\n");
		costSql.append(indentStr).append("        SELECT\n");
		costSql.append(indentStr).append("            day, week, month, year, collect, ad_show shownums, click clicknums, ad_id adid, ad_account advertiserid, convert_name convertName, deep_convert deepConvert, COALESCE(rude_cost, 0) rudeCost, COALESCE(cost,0) cost\n");
		costSql.append(indentStr).append("        FROM\n");
		costSql.append(indentStr).append("            ").append(yunYingProperties.getAdidrebatetable()).append(" ard\n");
		costSql.append(indentStr).append("        WHERE ad_id <> '' \n");
		costSql.append(indentStr).append("            AND day >= ").append(req.getStartTime()).append(" AND day <= ").append(req.getEndTime());
		if (StringUtils.isNotBlank(req.getAdAccount())) {
			costSql.append(" AND ad_account IN ('").append(req.getAdAccount().replaceAll(",", "','")).append("')"); // -- 广告权限
		}
		costSql.append(this.directAdvertiseWhereSnippet(req, "")).append('\n');
		costSql.append(indentStr).append("    ) ard\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT os, game_main pgid, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id FROM ").append(yunYingProperties.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ap.parentchl AND wpc.chncode = ap.chl\n");
		costSql.append(indentStr).append("WHERE\n");
		costSql.append(indentStr).append("    1 = 1").append(this.selectCommonCondition(req, StringUtils.EMPTY)).append('\n');
		costSql.append(indentStr).append("GROUP BY\n");
		// group by 条件
		costSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			costSql.append(", ").append(req.getQueryColumn());
		}
		costSql.append('\n');
		return costSql;
	}


	private StringBuilder getPeriodPaySql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder periodPaySql = new StringBuilder();
		periodPaySql.append(indentStr).append("SELECT\n");
		periodPaySql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		periodPaySql.append(indentStr).append("    COUNT(DISTINCT IF(fee > 0 OR givemoney > 0, kid, NULL)) periodPayCount, -- 期内设备付费数\n");
		periodPaySql.append(indentStr).append("    SUM(fee) periodPayFee1, -- 期内充值实付金额（分成前）\n");
		periodPaySql.append(indentStr).append("    SUM(fee * sharing) periodPayFee2, -- 期内充值实付金额（分成后）\n");
		periodPaySql.append(indentStr).append("    SUM(givemoney) periodPayGivemoney1, -- 期内充值代金券金额（分成前）\n");
		periodPaySql.append(indentStr).append("    SUM(givemoney * sharing) periodPayGivemoney2 -- 期内充值代金券金额（分成后）\n");
		periodPaySql.append(indentStr).append("FROM\n");
		periodPaySql.append(indentStr).append("    (\n");
		periodPaySql.append(indentStr).append("        SELECT\n");
		periodPaySql.append(indentStr).append("            d_reg_day day, d_reg_week week, d_reg_month month, d_reg_year year, d_kid kid, collect, d_game_main pgid, d_os os, d_game_sub gameid, d_chl_main parentchl, d_chl_sub chl, d_chl_base appchl, d_ad_id adid, d_ad_account advertiserid,\n");
		periodPaySql.append(indentStr).append("            d_convert_name convertName, d_deep_convert deepConvert, fee, givemoney\n");
		periodPaySql.append(indentStr).append("        FROM\n");
		periodPaySql.append(indentStr).append("            ").append(yunYingProperties.getDeviceregisterlogintable()).append(" pay\n");
		periodPaySql.append(indentStr).append("        WHERE\n");
		periodPaySql.append(indentStr).append("            d_spread_type = 1 AND d_ad_id <> '' AND d_reg_day >= ").append(req.getStartTime()).append(" AND d_reg_day <= ").append(req.getEndTime()).append('\n');
		periodPaySql.append(indentStr).append("            AND active_day >= ").append(req.getStartTime()).append(" AND active_day <= ").append(req.getEndTime())
				.append(this.indirectDeviceRegisterWhereSnippet(req, " ")).append(this.indirectAdvertiseWhereSnippet(req, " ")).append('\n');
		periodPaySql.append(indentStr).append("    ) ub\n");
		periodPaySql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game) pg ON ub.pgid = pg.id\n");
		periodPaySql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ub.parentchl AND wpc.chncode = ub.chl\n");
		periodPaySql.append(indentStr).append("WHERE\n");
		periodPaySql.append(indentStr).append("    1 = 1");
		periodPaySql.append(indentStr).append("GROUP BY\n");
		periodPaySql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			periodPaySql.append(", ").append(req.getQueryColumn());
		}
		periodPaySql.append("\n"); // 分组条件

		return periodPaySql;
	}


	private StringBuilder getActiveDeviceSql(AdPlanOverviewDto req, String indentStr) {
		StringBuilder activeDeviceSql = new StringBuilder();

		activeDeviceSql.append(indentStr).append("SELECT\n");
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append(StringUtils.isNotBlank(req.getQueryColumn()) ? req.getQueryColumn() + ", " : StringUtils.EMPTY).append('\n');
		activeDeviceSql.append(indentStr).append("    COUNT(DISTINCT kid_day) activedevices, -- 活跃设备数\n");
		activeDeviceSql.append(indentStr).append("    COUNT(DISTINCT IF(fee > 0 OR givemoney > 0, kid_day, NULL)) activepaydevices, -- 活跃付费设备数\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee) activetotalfee, -- 活跃充值实付金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee * sharing) activesharfee, -- 活跃充值实付金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney * sharing) activeshargivemoney, -- 活跃充值代金券金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("    SUM(pay_num) payNum -- 活跃付费次数\n");
		activeDeviceSql.append(indentStr).append("FROM\n");
		activeDeviceSql.append(indentStr).append("    (\n");
		activeDeviceSql.append(indentStr).append("        SELECT\n");
		activeDeviceSql.append(indentStr).append("            d_kid kid, concat(kid, '-', toString(day)) kid_day, active_day day, week, month, year, collect, d_ad_id adid, d_ad_account advertiserid, d_convert_name convertName, d_deep_convert deepConvert,\n");
		activeDeviceSql.append(indentStr).append("            d_os os, d_game_main pgid, d_game_sub gameid, d_chl_main parentchl, d_chl_sub chl, d_chl_base appchl, fee, givemoney,pay_num\n");
		activeDeviceSql.append(indentStr).append("        FROM\n");
		activeDeviceSql.append(indentStr).append("            ").append(yunYingProperties.getDeviceregisterlogintable()).append(" pay\n");
		activeDeviceSql.append(indentStr).append("        WHERE\n");
		activeDeviceSql.append(indentStr).append("            d_spread_type = 1 AND d_ad_id <> '' AND active_day >= ").append(req.getStartTime())
				.append(" AND active_day <= ").append(req.getEndTime()).append(this.indirectDeviceRegisterWhereSnippet(req, " "))
				.append(this.indirectAdvertiseWhereSnippet(req, " ")).append('\n');
		activeDeviceSql.append(indentStr).append("    ) ub\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id as Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game) pg ON ub.pgid = pg.id\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name, '-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ub.parentchl AND wpc.chncode = ub.chl\n");
		activeDeviceSql.append(indentStr).append("WHERE\n");
		activeDeviceSql.append(indentStr).append("    1 = 1");
		activeDeviceSql.append(indentStr).append("GROUP BY\n");
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			activeDeviceSql.append(", ").append(req.getQueryColumn());
		}
		activeDeviceSql.append("\n"); // 分组条件

		return activeDeviceSql;
	}

	//类别参数
	private StringBuilder getQueryColumnSql(AdPlanOverviewDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		sql.append(queryColumnSql);
		return sql;
	}

	//分组类别条件
	private StringBuilder getGroupColumnSql(AdPlanOverviewDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder sql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();
		sql.append(groupColumnSql);
		return sql;
	}

	// 计划
	public String directAdvertiseWhereSnippet(AdPlanOverviewDto req, String bieming) {

		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(req.getAdId())) {
			commCondSB.append(" AND ad_id IN ('").append(req.getAdId().replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(req.getAdAccount())) {
			commCondSB.append(" AND ad_account IN ('").append(req.getAdAccount().replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}

	// 计划
	public String indirectAdvertiseWhereSnippet(AdPlanOverviewDto req, String bieming) {
		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(req.getAdId())) {
			commCondSB.append(" AND d_ad_id IN ('").append(req.getAdId().replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}

	/**
	 * 设备注册Where片段
	 *
	 * @param req
	 * @param bieming
	 * @return
	 */
	public String directDeviceRegisterWhereSnippet(AdPlanOverviewDto req, String bieming) {
		final String parentchlArr = req.getParentchlArr();
		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND chl_main IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}
		return commCondSB.toString();
	}

	public String indirectDeviceRegisterWhereSnippet(AdPlanOverviewDto req, String bieming) {
		final String parentchlArr = req.getParentchlArr();
		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND d_chl_main IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}
		return commCondSB.toString();
	}

	//筛选条件  类别指标筛选
	public String selectCommonCondition(AdPlanOverviewDto req, String bieming) {
		final String parentchlArr = req.getParentchlArr();
		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}
		return commCondSB.toString();
	}

	private String getCostWhereSql(AdPlanOverviewDto record) {
		final String firstRoiMin = record.getFirstRoiMin();
		final String firstRoiMax = record.getFirstRoiMax();
		final String threeRoiMin = record.getThreeRoiMin();
		final String threeRoiMax = record.getThreeRoiMax();
		final String sevenRoiMin = record.getSevenRoiMin();
		final String sevenRoiMax = record.getSevenRoiMax();
		final String fifteenRoiMin = record.getFifteenRoiMin();
		final String fifteenRoiMax = record.getFifteenRoiMax();
		final String thirtyRoiMin = record.getThirtyRoiMin();
		final String thirtyRoiMax = record.getThirtyRoiMax();
		final String regCostMin = record.getRegCostMin();
		final String regCostMax = record.getRegCostMax();
		final String firstPayCostMin = record.getFirstPayCostMin();
		final String firstPayCostMax = record.getFirstPayCostMax();
		final String totalPayCostMin = record.getTotalPayCostMin();
		final String totalPayCostMax = record.getTotalPayCostMax();

		final StringBuilder builder = new StringBuilder();
		if (StringUtils.isNotBlank(firstRoiMin)){
			builder.append(" AND roi1 >= ").append(firstRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(firstRoiMax)){
			builder.append(" AND roi1 <= ").append(firstRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(threeRoiMin)){
			builder.append(" AND roi3 >= ").append(threeRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(threeRoiMax)){
			builder.append(" AND roi3 <= ").append(threeRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(sevenRoiMin)){
			builder.append(" AND roi7 >= ").append(sevenRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(sevenRoiMax)){
			builder.append(" AND roi7 <= ").append(sevenRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(fifteenRoiMin)){
			builder.append(" AND roi15 >= ").append(fifteenRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(fifteenRoiMax)){
			builder.append(" AND roi15 <= ").append(fifteenRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(thirtyRoiMin)){
			builder.append(" AND roi30 >= ").append(thirtyRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(thirtyRoiMax)){
			builder.append(" AND roi30 <= ").append(thirtyRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(regCostMin)){
			builder.append(" AND deviceCose >= ").append(regCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(regCostMax)){
			builder.append(" AND deviceCose <= ").append(regCostMax).append(" \n");
		}
		if (StringUtils.isNotBlank(firstPayCostMin)){
			builder.append(" AND payCose1 >= ").append(firstPayCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(firstPayCostMax)){
			builder.append(" AND payCose1 <= ").append(firstPayCostMax).append(" \n");
		}
		if (StringUtils.isNotBlank(totalPayCostMin)){
			builder.append(" AND payCoseAll >= ").append(totalPayCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(totalPayCostMax)){
			builder.append(" AND payCoseAll <= ").append(totalPayCostMax).append(" \n");
		}
		return builder.toString();
	}
}
